package com.example.demo.util;

import com.example.demo.entity.*;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.List;

public class WarnNumUtil {

        public  void exportExcel(HttpServletResponse response, List<WarnNum> data) {
            String[] str={"学号", "姓名", "年级","学院", "专业","轻度预警次数","中度预警次数",
                    "重度预警次数","总计"};
            try {
                //实例化HSSFWorkbook
                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建一个Excel表单，参数为sheet的名字
                HSSFSheet sheet = workbook.createSheet("sheet");
                //设置表头
                setTitle(workbook, sheet, str);
                //设置单元格并赋值
                setData(sheet, data);
                String filename=new String("1111.xls".getBytes(), StandardCharsets.UTF_8);
                //设置浏览器下载
                setBrowser(response, workbook,filename );
                System.out.println("导出解析成功!");
            } catch (Exception e) {
                System.out.println("导出解析失败!");
                e.printStackTrace();
            }
        }

        /**
         * setTitle设置标题
         * */
        private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
            try {
                HSSFRow row = sheet.createRow(0);
                //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
                for (int i = 0; i <= str.length; i++) {
                    sheet.setColumnWidth(i, 15 * 256);
                }
                //设置为居中加粗,格式化时间格式
                HSSFCellStyle style = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setBold(true);
                style.setFont(font);
                style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
                //创建表头名称
                HSSFCell cell;
                for (int j = 0; j < str.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(str[j]);
                    cell.setCellStyle(style);
                }
            } catch (Exception e) {
                System.out.println("导出时设置表头失败！");
                e.printStackTrace();
            }
        }

        /**
         * 方法名：setData
         * 功能：表格赋值
         */
        private static void setData(HSSFSheet sheet, List<WarnNum> data) {
            try{
                int rowNum = 1;
                for (int i = 0; i < data.size(); i++) {
                    HSSFRow row = sheet.createRow(rowNum);
                    for (int j = 0; j < 9; j++) {
                        if (j == 0) row.createCell(j).setCellValue(data.get(i).getId());
                        else if (j == 1) row.createCell(j).setCellValue(data.get(i).getName());
                        else if (j == 2) row.createCell(j).setCellValue(data.get(i).getGrade());
                        else if (j == 3) row.createCell(j).setCellValue(data.get(i).getCollege());
                        else if (j == 4) {
                                row.createCell(j).setCellValue(data.get(i).getMajor());
                        } else if (j == 5) {
                                row.createCell(j).setCellValue(data.get(i).getItem1());
                        } else if (j == 6) {
                            row.createCell(j).setCellValue(data.get(i).getItem2());
                        } else if (j == 7) {
                            row.createCell(j).setCellValue(data.get(i).getItem3());
                        }else row.createCell(j).setCellValue(data.get(i).getTotal());
                    }
                    rowNum++;
                }
                System.out.println("表格赋值成功！");
            }catch (Exception e){
                System.out.println("表格赋值失败！");
                e.printStackTrace();
            }
        }

        /**
         * 方法名：setBrowser
         * 功能：使用浏览器下载
         */
        private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
            try {
                //清空response
                //response.reset();
                //设置response的Header
                // response.addHeader("Access-Control-Allow-Origin","*");
                response.setCharacterEncoding("utf8");
                response.setContentType("application/vnd.ms-excel");
                response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
                OutputStream os = new BufferedOutputStream(response.getOutputStream());
                //将excel写入到输出流中
                workbook.write(os);
                os.flush();
                os.close();
                System.out.println("设置浏览器下载成功！");
            } catch (Exception e) {
                System.out.println("设置浏览器下载失败！");
                e.printStackTrace();
            }

        }

}
